package mytest.wd3;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;

public class Test_3 {

    public static void main(String[] args) {
        String sql1 = "select dial_task_gid,user_gid \n" +
                "  from log_dial_task_assign_%s \n" +
                "  where (end_time is null and create_time <= 1728082799 ) or (create_time <= 1728082799  and end_time >= 1727996400) ";

        String sql2 = "select task_id,user_classify,business_id,cus_cat from t_dial_task_%s";

        String sql3 = "select task_id,user_id,call_length\n" +
                "    from t_call_log_%s \n" +
                "  where start_time >= 1727996400000 and start_time <= 1728082799000";
        int n = 5; // 示例中 n 为 3
        String dateStr = "2024-10-26";
        DateTimeFormatter formatter = DateTimeFormatter.ISO_LOCAL_DATE;
        DateTimeFormatter formatter2 = DateTimeFormatter.ofPattern("yyyyMM");
        LocalDate date = LocalDate.parse(dateStr, formatter);
        LocalDate yesterday = LocalDate.now().minusDays(1);

        LocalDate startMonthFixed = date.minusMonths(n - 1); // 固定的起始月份

        for (int i = 0; i < n; i++) {
            String previousDay = date.minusMonths(i).format(formatter);
            LocalDate datePrevious = LocalDate.parse(previousDay, formatter);
            String previousMonth = date.minusMonths(i).format(formatter2);
            String s1 = String.format(sql1, previousMonth);
            System.out.println(s1);
            System.out.println(datePrevious);

            // 修改后的 s2 逻辑
            String startMonth = startMonthFixed.format(formatter2); // 固定的起始月份
            String endMonth = previousMonth; // 当前 s1 所在的月份

            StringBuilder s2 = new StringBuilder();
            LocalDate currentMonthS2 = LocalDate.parse(startMonth + "01", DateTimeFormatter.ofPattern("yyyyMMdd"));
            LocalDate endMonthDateS2 = LocalDate.parse(endMonth + "01", DateTimeFormatter.ofPattern("yyyyMMdd"));

            while (!currentMonthS2.isAfter(endMonthDateS2)) {
                String monthStr = currentMonthS2.format(formatter2);
                s2.append(String.format(sql2, monthStr));
                if (!currentMonthS2.equals(endMonthDateS2)) {
                    s2.append("\nunion all\n");
                }
                currentMonthS2 = currentMonthS2.plusMonths(1);
            }
            System.out.println(s2);

            LocalDate startDate = datePrevious.withDayOfMonth(1);
            LocalDate endDate = yesterday.withDayOfMonth(1);
            StringBuilder s3 = new StringBuilder();
            LocalDate currentMonthS3 = startDate;
            while (!currentMonthS3.isAfter(endDate)) {
                String monthStr = currentMonthS3.format(formatter2);
                s3.append(String.format(sql3, monthStr));
                if (!currentMonthS3.equals(endDate)) {
                    s3.append("\nunion all\n");
                }
                currentMonthS3 = currentMonthS3.plusMonths(1);
            }
            System.out.println(s3);

            System.out.println("=========================================");
        }
    }

}
